package team.sun.sss.dao.hardwork;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;
import team.sun.sss.pojo.hardwork.HwTime;

import java.util.List;

@Mapper
@Repository
public interface HwTimeDao {
//当学生申请表被学工审批通过，用人单位工时表会产生相关的名单
@Insert("insert into hw_work_time(stu_id,labor_unit,batch_team)  " +
        "values(#{stuId},#{laborUnit},#{batchTeam})")
int insertTimeTable(int stuId, String laborUnit,String batchTeam );
    //3.1用人单位填写时间时显示以往的工时
    @Select("select * from hw_work_time where stu_id=#{stuId} and  labor_unit=#{laborUnit} and batch_team=#{batchTeam}" )
    List<HwTime> getStuHwTime(int stuId,String laborUnit,String batchTeam);
    //3.用人单位添加学生的工时
    @Select("select DISTINCT stu_id from hw_work_time " )
    List<HwTime> getStuIdNum();
    @Select("select DISTINCT batch_team from hw_work_time " )
    List<HwTime> getbatchNum();
    @Insert("insert into hw_work_time(stu_id,labor_unit,batch_team,hw_time_year,hw_time_month, "+
            "            hw_time_hour,hw_time_salary,hw_time_all_salary,hw_time_submit_time,hw_time_apply_state) " +
            "            values(#{stuId},#{laborUnit},#{batchTeam},#{hwTimeYear},#{hwTimeMonth}," +
            "            #{hwTimeHour},#{hwTimeSalary},#{hwTimeAllSalary},#{hwTimeSubmitTime},\"未审核\")")
    int addHwTime(int stuId,String laborUnit,String batchTeam,int hwTimeYear,int hwTimeMonth,int hwTimeHour,float hwTimeSalary,
                  float hwTimeAllSalary,String hwTimeSubmitTime );
    //4.用人单位填写工时
    @Update("update hw_work_time set hw_time_year=#{hwTimeYear}," +
            "hw_time_month=#{hwTimeMonth},hw_time_hour=#{hwTimeHour},hw_time_salary=#{hwTimeSalary},hw_time_all_salary=#{hwTimeAllSalary} " +
            ",hw_time_submit_time=#{hwTimeSubmitTime},hw_time_apply_state=\"未审核\"  where hw_time_num=#{hwTimeNum}")
    int updateHwLabor(int hwTimeNum,int hwTimeYear,int hwTimeMonth,int hwTimeHour,float hwTimeSalary,
                      float hwTimeAllSalary,String hwTimeSubmitTime);
    //学工审核工时
    @Update("update hw_work_time set hw_time_apply_state=#{hwTimeApplyState}  where hw_time_num=#{hwTimeNum}")
    int updateHwLaborState(int hwTimeNum,String hwTimeApplyState);
    //三表联立
    @Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state " +
            "from student,hw_work_time,hw_apply " +
            "where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id " +
            "and hw_apply.hwa_apply_state=\"审核通过\" " +
            "and hw_apply.batch_team=hw_work_time.batch_team " )
    List<HwTime> threeTableTime();
    //三表联立之根据num
    @Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state\n" +
            "         from student,hw_work_time,hw_apply\n" +
            "           where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id and " +
            "hw_apply.batch_team=hw_work_time.batch_team  and hw_work_time.hw_time_num=#{hwTimeNum}" )
    List<HwTime> threeTableTimeDetail(int hwTimeNum);
    //学生端三表联立之根据学号
    @Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state " +
            "from student,hw_work_time,hw_apply " +
            "where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id " +
            "and hw_work_time.hw_time_apply_state=\"审核通过\" " +
            "and hw_apply.batch_team=hw_work_time.batch_team " +
            "and student.stu_id=#{stuId} " )
    List<HwTime> stuTime(int stuId);
//根据姓名查询
@Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state " +
        "from student,hw_work_time,hw_apply " +
        "where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id " +
        "and hw_apply.hwa_apply_state=\"审核通过\" " +
        "and hw_apply.batch_team=hw_work_time.batch_team " +
        "and student.stu_name=#{stuName} " )
List<HwTime> selectByName(String stuName);
//根据学号查询
@Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state " +
        "from student,hw_work_time,hw_apply " +
        "where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id " +
        "and hw_apply.hwa_apply_state=\"审核通过\" " +
        "and hw_apply.batch_team=hw_work_time.batch_team " +
        "and student.stu_id=#{stuId} " )
List<HwTime> selectByStuId(int stuId);
//根据用工批次和部门查询
@Select("select student.stu_name,student.stu_college,student.stu_grade,hw_work_time.* ,hw_apply.hwa_work_state " +
        "from student,hw_work_time,hw_apply " +
        "where student.stu_id=hw_work_time.stu_id and hw_work_time.stu_id=hw_apply.stu_id " +
        "and hw_apply.hwa_apply_state=\"审核通过\" " +
        "and hw_apply.batch_team=hw_work_time.batch_team " +
        "and  hw_apply.hwa_unit=hw_work_time.labor_unit " +
        "and hw_apply.batch_team=#{batchTeam} " +
        "and hw_apply.hwa_unit=#{laborUnit} " )
List<HwTime> selectByBatch(String batchTeam,String laborUnit);

}
